package model

import (
    "database/sql"
    "fmt"
    "roy/common/data"
    "sort"
    "strconv"
    "strings"
)

// Model 查询参数
type Model struct {
    // Table 表名
    Table string
    // Data 要插入或修改的数据
    // 例: ["id": 1, "name": "roy", ...]
    Data map[string]interface{}
    // Where 条件
    // 数据结构: [关系符]=([字段名]=字段值)
    // 例: where["="]=(w["id"]=1)
    // 如果关系符为 "in" 或 "not in" 则值字段值可为数组
    Where map[string]map[string]any
    // List 要添加的多组数据
    // 例: [["id": 1, "name": "roy", ...], ["id": 2, "name": "roy", ...], ...]
    List []map[string]interface{}
    // Fields 查询字段
    // 例: ["id", "name", ...]
    Fields []string
    // Order 排序字段
    // 例: "id"
    Order string
    // OrderType 排序方式
    // 例: "desc"
    OrderType string
    // Limit 查询条数
    // 例: "10" 或 "0,10"
    Limit string
    // Sql 原生 sql
    Sql string
}

// Add 添加数据到数据库
func (m *Model) Add() (any, error) {
    var (
        sqlStr             = "INSERT INTO `" + m.Table + "` ("
        fieldStr, valueStr string
    )

    // 组装 sql 语句, List 插入多条数据, Data 插入一条数据
    if m.List != nil {
        // 取得数据列
        var columns []string
        for k := range m.List[0] {
            columns = append(columns, k)
        }
        sort.Strings(columns) // 排序列
        // 把列数组转为字符串
        fieldStr = strings.Join(columns, "`,`")
        fieldStr = "`" + fieldStr + "`"
        // 获取并封装列对应的值
        for _, fields := range m.List {
            valueStr += "("
            for _, column := range columns {
                // switch 判断数据值类型并作相应封装
                switch fields[column].(type) {
                case int:
                    v, _ := fields[column].(int)
                    valueStr += strconv.Itoa(v) + ","
                case int32:
                    v, _ := fields[column].(int32)
                    valueStr += strconv.Itoa(int(v)) + ","
                case int64:
                    v, _ := fields[column].(int64)
                    valueStr += strconv.Itoa(int(v)) + ","
                case float32:
                    v, _ := fields[column].(float32)
                    valueStr += strconv.FormatFloat(float64(v), 'f', 2, 64) + ","
                case float64:
                    v, _ := fields[column].(float64)
                    valueStr += strconv.FormatFloat(v, 'f', 2, 64) + ","
                default:
                    valueStr += "'" + fields[column].(string) + "',"
                }
            }
            // 去除末尾逗号(,)
            valueStr = strings.TrimRight(valueStr, ",")
            // 每条数据封装完成后添加括号
            valueStr += "),"
        }
        // 去除末尾逗号(,)
        valueStr = strings.TrimRight(valueStr, ",")
    } else if m.Data != nil {
        valueStr = "("
        for f, v := range m.Data {
            fieldStr += "`" + f + "`,"
            // switch 判断数据值类型并作相应封装
            switch v.(type) {
            case int:
                val, _ := v.(int)
                valueStr += strconv.Itoa(val) + ","
            case int32:
                val, _ := v.(int32)
                valueStr += strconv.Itoa(int(val)) + ","
            case int64:
                val, _ := v.(int64)
                valueStr += strconv.Itoa(int(val)) + ","
            case float32:
                val, _ := v.(float32)
                valueStr += strconv.FormatFloat(float64(val), 'f', '2', 64) + ","
            case float64:
                val, _ := v.(float64)
                valueStr += strconv.FormatFloat(val, 'f', '2', 64) + ","
            default:
                valueStr += "'" + v.(string) + "',"
            }
        }
        // 去除末尾逗号(,)
        valueStr = strings.TrimRight(valueStr, ",") + ")"
        fieldStr = strings.TrimRight(fieldStr, ",")
    } else {
        return 0, fmt.Errorf("非法参数")
    }
    // 组装要执行的 sql 语句
    sqlStr += fieldStr + ") values " + valueStr
    db, err := Db()
    defer db.Close()
    if err != nil {
        return 0, err
    }
    result, err := db.Exec(sqlStr)
    if err != nil {
        return 0, err
    }
    var res any
    if m.List != nil {
        // 如果是批量插入则返回受影响行数
        res, err = result.RowsAffected()
        if err != nil {
            return 0, err
        }
    } else {
        // 如果是单条插入则返回数据主键id(id可能是自增整数或者字符串)
        res, err = result.LastInsertId()
        if err != nil {
            return 0, err
        }
        id, ok := m.Data["id"]
        if res.(int64) == 0 && ok {
            return id, nil
        }
    }
    return res, nil
}

// Delete 删除
func (m *Model) Delete() (int64, error) {
    var sqlStr = "DELETE FROM "
    sqlStr += "`" + m.Table + "`"
    if m.Where != nil {
        sqlStr += getWhere(m.Where)
    }
    db, err := Db()
    defer db.Close()
    if err != nil {
        return 0, err
    }
    result, err := db.Exec(sqlStr)
    if err != nil {
        return 0, err
    }
    num, err := result.RowsAffected()
    if err != nil {
        return 0, err
    }
    return num, nil
}

// Update 修改
func (m *Model) Update() (int64, error) {
    var sqlStr = "UPDATE `" + m.Table + "` SET "
    // 封闭要修改的数据sql
    for f, v := range m.Data {
        sqlStr += "`" + f + "`="
        // switch 判断数据值类型并作相应封装
        switch v.(type) {
        case int:
            val, _ := v.(int)
            sqlStr += strconv.Itoa(val) + ","
        case int32:
            val, _ := v.(int32)
            sqlStr += strconv.Itoa(int(val)) + ","
        case int64:
            val, _ := v.(int64)
            sqlStr += strconv.Itoa(int(val)) + ","
        case float32:
            val, _ := v.(float32)
            sqlStr += strconv.FormatFloat(float64(val), 'f', 2, 64) + ","
        case float64:
            val, _ := v.(float64)
            sqlStr += strconv.FormatFloat(val, 'f', 2, 64) + ","
        default:
            sqlStr += "'" + v.(string) + "',"
        }
    }
    // 去除末尾逗号(,)
    sqlStr = strings.TrimRight(sqlStr, ",")
    if m.Where != nil {
        sqlStr += getWhere(m.Where)
    }
    db, err := Db()
    defer db.Close()
    if err != nil {
        return 0, err
    }
    result, err := db.Exec(sqlStr)
    if err != nil {
        return 0, err
    }
    num, err := result.RowsAffected()
    if err != nil {
        return 0, err
    }
    return num, nil
}

// GetList 获取数据列表
func (m *Model) GetList() ([]map[string]interface{}, error) {
    var sqlStr = "SELECT "
    if m.Fields != nil {
        fields := strings.Join(m.Fields, "`,`")
        sqlStr += "`" + fields + "`"
    } else {
        sqlStr += "*"
    }
    sqlStr += " FROM `" + m.Table + "`"
    if m.Where != nil {
        sqlStr += getWhere(m.Where)
    }
    if m.Order != "" {
        sqlStr += " ORDER BY `" + m.Order + "`"
        if m.OrderType != "" {
            sqlStr += " " + strings.ToUpper(m.OrderType)
        }
    }
    if m.Limit != "" {
        sqlStr += " LIMIT " + m.Limit
    }
    db, err := Db()
    defer db.Close()
    if err != nil {
        return nil, err
    }
    rows, err := db.Query(sqlStr)
    if err != nil {
        return nil, err
    }
    list, err := data.Rows2Array(rows)
    if err != nil {
        return nil, err
    }
    return list, nil
}

// GetModel 查询单条数据(返回 array)
func (m *Model) GetModel() (map[string]interface{}, error) {
    var sqlStr = "SELECT "
    if m.Fields != nil {
        fields := strings.Join(m.Fields, "`,`")
        sqlStr += "`" + fields + "`"
    } else {
        sqlStr += "*"
    }
    sqlStr += " FROM `" + m.Table + "`"
    if m.Where != nil {
        sqlStr += getWhere(m.Where)
    }
    if m.Order != "" {
        sqlStr += " ORDER BY `" + m.Order + "`"
        if m.OrderType != "" {
            sqlStr += " " + strings.ToUpper(m.OrderType)
        }
    }
    sqlStr += " LIMIT 1"
    db, err := Db()
    defer db.Close()
    if err != nil {
        return nil, err
    }
    //row := db.QueryRow(sql)
    rows, err := db.Query(sqlStr)
    if err != nil {
        return nil, err
    }
    arr, err := data.Rows2Array(rows)
    if err != nil {
        return nil, err
    }
    if len(arr) == 0 {
        return nil, nil
    }
    return arr[0], nil
}

// GetRow 查询单条数据(返回 sql.Row)
func (m *Model) GetRow() (*sql.Row, error) {
    var sqlStr = "SELECT "
    if m.Fields != nil {
        fields := strings.Join(m.Fields, "`,`")
        sqlStr += "`" + fields + "`"
    } else {
        sqlStr += "*"
    }
    sqlStr += " FROM `" + m.Table + "`"
    if m.Where != nil {
        sqlStr += getWhere(m.Where)
    }
    if m.Order != "" {
        sqlStr += " ORDER BY `" + m.Order + "`"
        if m.OrderType != "" {
            sqlStr += " " + strings.ToUpper(m.OrderType)
        }
    }
    sqlStr += " LIMIT 1"
    db, err := Db()
    defer db.Close()
    if err != nil {
        return nil, err
    }
    // 单条查询
    row := db.QueryRow(sqlStr)
    return row, nil
}

// Exec 执行原生sql增、删、改
func (m *Model) Exec() (int64, error) {
    sqlStr := strings.ToLower(m.Sql)
    if strings.Index(sqlStr, "insert") == -1 && strings.Index(sqlStr, "update") == -1 && strings.Index(sqlStr, "delete") == -1 {
        return 0, fmt.Errorf("非法sql语句")
    }
    db, err := Db()
    defer db.Close()
    if err != nil {
        return 0, err
    }
    result, err := db.Exec(sqlStr)
    if err != nil {
        return 0, err
    }
    num, err := result.RowsAffected()
    if err != nil {
        return 0, err
    }
    return num, nil
}

// Query 执行原生sql查询
func (m *Model) Query() ([]map[string]interface{}, error) {
    sqlStr := strings.ToLower(m.Sql)
    if strings.Index(sqlStr, "select") == -1 {
        return nil, fmt.Errorf("非法sql语句")
    }
    db, err := Db()
    defer db.Close()
    if err != nil {
        return nil, err
    }
    rows, err := db.Query(sqlStr)
    if err != nil {
        return nil, err
    }
    list, err := data.Rows2Array(rows)
    if err != nil {
        return nil, err
    }
    return list, err
}

// 封装 where 条件
func getWhere(where map[string]map[string]any) string {
    wStr := " WHERE "
    for c, w := range where {
        c = strings.ToUpper(c)
        ws := ""
        r := " AND "
        for f, v := range w {
            vs := ""
            if c == "IN" || c == "NOT IN" {
                vStr, ok1 := v.(string)
                vArr, ok2 := v.([]string)
                if ok1 {
                    vArr = strings.Split(vStr, ",")
                    vs = strings.Join(vArr, "','")
                } else if ok2 {
                    vs = strings.Join(vArr, "','")
                }
                vs = "('" + vs + "')"
            } else {
                switch v.(type) {
                case int:
                    val, _ := v.(int)
                    vs = strconv.Itoa(val)
                case int32:
                    val, _ := v.(int32)
                    vs = strconv.Itoa(int(val))
                case int64:
                    val, _ := v.(int64)
                    vs = strconv.Itoa(int(val))
                case float32:
                    val, _ := v.(float32)
                    vs = strconv.FormatFloat(float64(val), 'f', 2, 64)
                case float64:
                    val, _ := v.(float64)
                    vs = strconv.FormatFloat(val, 'f', 2, 64)
                default:
                    vs = "'" + v.(string) + "'"
                }
            }
            if c == "OR" {
                c = "="
                r = " OR "
            }
            if strings.Index(f, "|") > 0 {
                fs := strings.Split(f, "|")
                vst := ""
                for _, s := range fs {
                    vst += "`" + s + "` " + c + " " + vs + " OR "
                }
                ws += "(" + strings.TrimRight(vst, " OR ") + ")" + r
            } else {
                ws += "`" + f + "` " + c + " " + vs + r
            }
        }
        wStr += "(" + strings.TrimRight(ws, r) + ")" + " AND "
    }
    wStr = strings.TrimRight(wStr, " AND ")
    return wStr
}
